package com.wwdx.utils.excel;

import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.ComparatorUtils;
import org.apache.commons.collections.comparators.ComparableComparator;
import org.apache.commons.collections.comparators.ComparatorChain;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * The <code>ExcelUtil</code> 与 {@link ExcelCell}搭配使用
 * @author liguoliang
 * @date 2018-08-25 14:28:28
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于单个sheet
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
     * @param out     与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     */
    public static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out) {
        exportExcel(headers, dataset, out, null);
    }

    /**
     * 利用JAVA的反射机制，将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上<br>
     * 用于单个sheet
     * @param headers 表格属性列名数组
     * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
     *                javabean属性的数据类型有基本数据类型及String,Date,String[],Double[]
     * @param out     与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
     * @param pattern 如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
     */
    private static <T> void exportExcel(Map<String,String> headers, Collection<T> dataset, OutputStream out, String pattern) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet();
        write2Sheet(sheet, headers, dataset, pattern);
        try {
            workbook.write(out);
        } catch (IOException e) {
            logger.error("错误信息：{}",e.getMessage());
        }
    }

    /**
     * 每个sheet的写入
     *
     * @param sheet   页签
     * @param headers 表头
     * @param dataset 数据集合
     * @param pattern 日期格式
     */
    private static <T> void write2Sheet(HSSFSheet sheet, Map<String,String> headers,
                                        Collection<T> dataset, String pattern) {

        //时间格式默认"yyyy-MM-dd"
        if (StringUtils.isEmpty(pattern)){
            pattern = "yyyy-MM-dd";
        }
        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        //todo:标题行转中文
        Set<String> keys = headers.keySet();
        Iterator<String> it1 = keys.iterator();
        //存放临时键变量
        String key = StringUtils.EMPTY;
        //标题列数
        int c= 0;   
        while (it1.hasNext()){
            key = it1.next();
            if (headers.containsKey(key)) {
                HSSFCell cell = row.createCell(c);
                HSSFRichTextString text = new HSSFRichTextString(headers.get(key));
                cell.setCellValue(text);
                c++;
            }
        }

        // 遍历集合数据，产生数据行
        Iterator<T> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            T t = (T) it.next();
            if (t instanceof Map) {
                Map<String, Object> map = (Map<String, Object>) t;
                int cellNum = 0;
                //todo:遍历列名
                for (String key2 : keys) {
                    if (!headers.containsKey(key2)) {
                        continue;
                    }
                    Object value = map.get(key);
                    HSSFCell cell = row.createCell(cellNum);
                    String textValue = null;
                    if (value instanceof Integer) {
                        int intValue = (Integer) value;
                        cell.setCellValue(intValue);
                    } else if (value instanceof Float) {
                        float fValue = (Float) value;
                        cell.setCellValue(fValue);
                    } else if (value instanceof Double) {
                        double dValue = (Double) value;
                        cell.setCellValue(dValue);
                    } else if (value instanceof Long) {
                        long longValue = (Long) value;
                        cell.setCellValue(longValue);
                    } else if (value instanceof Boolean) {
                        boolean bValue = (Boolean) value;
                        cell.setCellValue(bValue);
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else if (value instanceof String[]) {
                        String[] strArr = (String[]) value;
                        execute(strArr,cell,row,cellNum);
                    } else if (value instanceof Double[]) {
                        execute(value,cell,row,cellNum);
                    } else {
                        // 其它数据类型都当作字符串简单处理
                        textValue = value == null ? "" : value.toString();
                    }
                    if (textValue != null) {
                        HSSFRichTextString richString = new HSSFRichTextString(textValue);
                        cell.setCellValue(richString);
                    }
                    cellNum++;
                }
            } else {
                List<FieldForSortting> fields = sortFieldByAnno(t.getClass());
                int cellNum = 0;
                try {
                    for (FieldForSortting fieldForSortting : fields) {
                        HSSFCell cell = row.createCell(cellNum);
                        Field field = fieldForSortting.getField();
                        field.setAccessible(true);
                        Object value = field.get(t);
                        String textValue = null;
                        if (value instanceof Integer) {
                            int intValue = (Integer) value;
                            cell.setCellValue(intValue);
                        } else if (value instanceof Float) {
                            float fValue = (Float) value;
                            cell.setCellValue(fValue);
                        } else if (value instanceof Double) {
                            double dValue = (Double) value;
                            cell.setCellValue(dValue);
                        } else if (value instanceof Long) {
                            long longValue = (Long) value;
                            cell.setCellValue(longValue);
                        } else if (value instanceof Boolean) {
                            boolean bValue = (Boolean) value;
                            cell.setCellValue(bValue);
                        } else if (value instanceof Date) {
                            Date date = (Date) value;
                            SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                            textValue = sdf.format(date);
                        } else if (value instanceof String[]) {
                            String[] strArr = (String[]) value;
                            execute(strArr,cell,row,cellNum);
                        } else if (value instanceof Double[]) {
                            execute(value,cell,row,cellNum);
                        } else {
                            // 其它数据类型都当作字符串简单处理
                            String empty = StringUtils.EMPTY;
                            ExcelCell anno = field.getAnnotation(ExcelCell.class);
                            if (anno != null) {
                                empty = anno.defaultValue();
                            }
                            textValue = value == null ? empty : value.toString();
                        }
                        if (textValue != null) {
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                        cellNum++;
                    }
                }catch (IllegalAccessException e){
                    logger.error("错误信息：{}",e.getMessage());
                }
            }
        }
        // 设定自动宽度
        for (int i = 0; i < headers.size(); i++) {
            sheet.autoSizeColumn(i);
        }
    }

    private static void execute(String[] strArr,HSSFCell cell,HSSFRow row,int cellNum){
        for (int j = 0; j < strArr.length; j++) {
            String str = strArr[j];
            cell.setCellValue(str);
            if (j != strArr.length - 1) {
                cellNum++;
                cell = row.createCell(cellNum);
            }
        }
    }

    private static void execute(Object value,HSSFCell cell,HSSFRow row,int cellNum){
        Double[] douArr = (Double[]) value;
        for (int j = 0; j < douArr.length; j++) {
            Double val = douArr[j];
            // 值不为空则set Value
            if (val != null) {
                cell.setCellValue(val);
            }
            if (j != douArr.length - 1) {
                cellNum++;
                cell = row.createCell(cellNum);
            }
        }
    }

    /**
     * 根据annotation的seq排序后的栏位
     */
    private static List<FieldForSortting> sortFieldByAnno(Class<?> clazz) {
        Field[] fieldsArr = clazz.getDeclaredFields();
        List<FieldForSortting> fields = new ArrayList<>();
        for (Field field : fieldsArr) {
            ExcelCell ec = field.getAnnotation(ExcelCell.class);
            if (ec == null) {
                // 没有ExcelCell Annotation 视为不汇入
                continue;
            }
            int id = ec.index();
            fields.add(new FieldForSortting(field, id));
        }
        sortByProperties(fields,  "index");
        return fields;
    }

    private static void sortByProperties(List list, String... props) {
        if (CollectionUtils.isNotEmpty(list)) {
            Comparator typeComp = ComparableComparator.getInstance();
            typeComp = ComparatorUtils.nullHighComparator(typeComp);
            List sortCols = new ArrayList();
            if (props != null) {
                for (String prop : props) {
                    sortCols.add(new BeanComparator(prop, typeComp));
                }
            }
            if (sortCols.size() > 0) {
                list.sort(new ComparatorChain(sortCols));
            }
        }
    }
}